<?php
/**
 * 订单管理
 * Created by PhpStorm.
 * User: g
 * Date: 2018/4/15
 * Time: 10:16
 */

namespace app\index\controller;

use app\index\validate\Valid;
use think\Request;
use think\Db;
use PHPExcel;
use PHPExcel_IOFactory;
use PHPExcel_Writer_Excel5;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;
class Ddgl extends Common
{
    /**
     * 销售订单管理
     */
    public function xsddgl(){
        $status = $this->request->param('status');
        $zoning = $this->request->param('zoning');
        $provice = $this->request->param('provice');
        $city = $this->request->param('city');
        $sqsj_start = $this->request->param('sqsj_start');
        $sqsj_end = $this->request->param('sqsj_end');
        $order_sn = $this->request->param('order_sn');
        $jefw_start = $this->request->param('jefw_start');
        $jefw_end = $this->request->param('jefw_end');
        $spgdsj_start = $this->request->param('spgdsj_start');
        $spgdsj_end = $this->request->param('spgdsj_end');
        $jxsbh = $this->request->param('jxsbh');
        $sqzt = $this->request->param('sqzt');
        $fhsj_start = $this->request->param('fhsj_start');
        $fhsj_end = $this->request->param('fhsj_end');
        $jxsmc = $this->request->param('jxsmc');
        $fhzt = $this->request->param('fhzt');
        $ygdhsj_start = $this->request->param('ygdhsj_start');
        $ygdhsj_end = $this->request->param('ygdhsj_end');
        $qdyhzcbh = $this->request->param('qdyhzcbh');
        $shzt = $this->request->param('shzt');
        $dhsj_start = $this->request->param('dhsj_start');
        $dhsj_end = $this->request->param('dhsj_end');
        $sqr = $this->request->param('sqr');
        $fplx = $this->request->param('fplx');
        $this->assign([
           'status'=>$status,
           'zoning'=>$zoning,
           'provice'=>$provice,
           'city'=>$city,
           'sqsj_start'=>$sqsj_start,
           'sqsj_end'=>$sqsj_end,
           'order_sn'=>$order_sn,
           'jefw_start'=>$jefw_start,
           'jefw_end'=>$jefw_end,
           'spgdsj_start'=>$spgdsj_start,
           'spgdsj_end'=>$spgdsj_end,
           'jxsbh'=>$jxsbh,
           'sqzt'=>$sqzt,
           'fhsj_start'=>$fhsj_start,
           'fhsj_end'=>$fhsj_end,
           'jxsmc'=>$jxsmc,
           'fhzt'=>$fhzt,
           'ygdhsj_start'=>$ygdhsj_start,
           'ygdhsj_end'=>$ygdhsj_end,
           'qdyhzcbh'=>$qdyhzcbh,
           'shzt'=>$shzt,
           'dhsj_start'=>$dhsj_start,
           'dhsj_end'=>$dhsj_end,
           'sqr'=>$sqr,
           'fplx'=>$fplx,
        ]);
        $where = [];
        if($status){
            $where['status'] = $status;
        }
        if($zoning){
            $where['zoning'] = ['like','%'.$zoning.'%'];
        }
        if($provice){
            $where['provice'] = ['like','%'.$provice.'%'];
        }
        if($city){
            $where['city'] = ['like','%'.$city.'%'];
        }
        if($sqsj_start&&!$sqsj_end){
            $where['sqrq'] = ['>',strtotime($sqsj_start)];
        }elseif(!$sqsj_start&&$sqsj_end){
            $where['sqrq'] = ['<',strtotime($sqsj_end)];
        }elseif($sqsj_start&&$sqsj_end){
            $where['sqrq'] = [['>',strtotime($sqsj_start)],['<',strtotime($sqsj_end)],'and'];
        }
        if($order_sn){
            $where['sn'] = ['like','%'.$order_sn.'%'];
        }

        if($jefw_start&&!$jefw_end){
            $where['yszje'] = ['>',$jefw_start];
        }elseif(!$jefw_start&&$jefw_end){
            $where['yszje'] = ['<',$jefw_end];
        }elseif($jefw_start&&$jefw_end){
            $where['yszje'] = [['>',$jefw_start],['<',$jefw_end],'and'];
        }
        if($spgdsj_start&&!$spgdsj_end){
            $where['spgdsj'] = ['>',$spgdsj_start];
        }elseif(!$spgdsj_start&&$spgdsj_end){
            $where['spgdsj'] = ['<',$spgdsj_end];
        }elseif($spgdsj_start&&$spgdsj_end){
            $where['spgdsj'] = [['>',$spgdsj_start],['<',$spgdsj_end],'and'];
        }
        if($jxsbh){
            $where['khbh'] = ['like','%'.$jxsbh.'%'];
        }
        $count = db('cg_order')->where($where)->count();
        $data = db('cg_order')->where($where)->order('id desc')->paginate(10,$count);
        $page = $data->render();
        $data = $data->items();
         $maxpage=ceil($count/10);
        foreach($data as $k=>$v){
            $v['fpType'] = $v['fpType']==1?'普通发票':'增值税发票';
            $v['sqzt_title'] = db('cg_order_status')->where('id',$v['sqzt'])->value('title');
            $v['fhzt'] = '';
            $v['fhsj'] = '';
            $v['ygdhsj'] = '';
            $v['shzt'] = '';
            $v['shsj'] = '';
            $v['xs'] = '';
            $v['je'] = '';
            $data[$k] = $v;
        }
        $this->assign([
            'data'=>$data,
            'page'=>$page,
            'maxpage'=>$maxpage,
            'count'=>$count
        ]);
        return $this->fetch();
    }

    /**
     * 修改收货地址--经销商或者网点
     */
    public function delivery_address(){
        $id = $this->request->param('id');
        if($_POST){
            $addr_info = $this->request->param('addr_info');
            $cusName = db('cg_order')->where('id',$id)->value('cusName');
            $cusType = db('cg_order')->where('id',$id)->value('cusType');
            if($cusType==1){
                $shrxx = db('cg_jxs')->where('id',$cusName)->field('lgs_rcv_addr,lgs_rcv_one,lgs_rcv_phone,lgs_rcv_addr2,lgs_rcv_one2,lgs_rcv_phone2,lgs_rcv_addr3,lgs_rcv_one3,lgs_rcv_phone3')->find();

            }else{
                $shrxx = db('cg_net')->where('id',$cusName)->field('lgs_rcv_addr,lgs_rcv_one,lgs_rcv_phone,lgs_rcv_addr2,lgs_rcv_one2,lgs_rcv_phone2,lgs_rcv_addr3,lgs_rcv_one3,lgs_rcv_phone3')->find();
            }
            if($addr_info==1){
                $insert = [];
                $insert['shdz'] = $shrxx['lgs_rcv_addr'];
                $insert['shr'] = $shrxx['lgs_rcv_one'];
                $insert['dh'] = $shrxx['lgs_rcv_phone'];
            }elseif($addr_info==2){
                $insert = [];
                $insert['shdz'] = $shrxx['lgs_rcv_addr2'];
                $insert['shr'] = $shrxx['lgs_rcv_one2'];
                $insert['dh'] = $shrxx['lgs_rcv_phone2'];
            }elseif($addr_info==3){
                $insert = [];
                $insert['shdz'] = $shrxx['lgs_rcv_addr3'];
                $insert['shr'] = $shrxx['lgs_rcv_one3'];
                $insert['dh'] = $shrxx['lgs_rcv_phone3'];
            }
            $bool = db('cg_order')->where('id',$id)->update($insert);
            if(false!==$bool){
                echo json_encode(['success'=>true,'msg'=>'ok']);
            }else{
                echo json_encode(['success'=>false,'msg'=>'error']);
            }
            die;
        }
        $data = db('cg_order')->where('id',$id)->field('khmc,khbh,sn,sqrq,yszje')->find();
        $data['zsl'] = db('cg_order_list')->where('order_id',$id)->sum('num');
        $this->assign('data',$data);
        $cusName = db('cg_order')->where('id',$id)->value('cusName');
        $cusType = db('cg_order')->where('id',$id)->value('cusType');
        if($cusType==1){
            $shrxx = db('cg_jxs')->where('id',$cusName)->field('lgs_rcv_addr,lgs_rcv_one,lgs_rcv_phone,lgs_rcv_addr2,lgs_rcv_one2,lgs_rcv_phone2,lgs_rcv_addr3,lgs_rcv_one3,lgs_rcv_phone3')->find();

        }else{
            $shrxx = db('cg_net')->where('id',$cusName)->field('lgs_rcv_addr,lgs_rcv_one,lgs_rcv_phone,lgs_rcv_addr2,lgs_rcv_one2,lgs_rcv_phone2,lgs_rcv_addr3,lgs_rcv_one3,lgs_rcv_phone3')->find();
        }
        $this->assign('shrxx',$shrxx);
        return $this->fetch();
    }

    /**
     * 订单作废
     */
    public function order_del(){
        $id = $this->request->param('id');
        $bool = db('cg_order')->where('id',$id)->update(['sqzt'=>8]);
        if(false!==$bool){
            echo json_encode(['success'=>true,'msg'=>'ok']);
        }else{
            echo json_encode(['success'=>false,'msg'=>'error']);
        }
    }
    /**
     * 销售订单明细查询
     */
    public function xsddmxcx(){
        $zoning = $this->request->param('zoning');
        $provice = $this->request->param('provice');
        $city = $this->request->param('city');
        $order_sn = $this->request->param('order_sn');
        $khbh = $this->request->param('khbh');
        $khmc = $this->request->param('khmc');
        $sqzt = $this->request->param('sqzt');
        $kind = $this->request->param('kind');
        $xslx = $this->request->param('xslx');
        $sku = $this->request->param('sku');
        $prod_sn = $this->request->param('prod_sn');
        $prod_name = $this->request->param('prod_name');
        $category = $this->request->param('category');
        $start = $this->request->param('start');
        $end = $this->request->param('end');
        $this->assign([
            'zoning'=>$zoning,
            'provice'=>$provice,
            'city'=>$city,
            'order_sn'=>$order_sn,
            'khbh'=>$khbh,
            'khmc'=>$khmc,
            'sqzt'=>$sqzt,
            'kind'=>$kind,
            'xslx'=>$xslx,
            'sku'=>$sku,
            'prod_sn'=>$prod_sn,
            'prod_name'=>$prod_name,
            'category'=>$category,
            'start'=>$start,
            'end'=>$end,
        ]);
        $where = [];
        if($zoning){
            $where['t2.zoning'] = ['like','%'.$zoning.'%'];
        }
        if($provice){
            $where['t2.provice'] = ['like','%'.$provice.'%'];
        }
        if($city){
            $where['t2.city'] = ['like','%'.$city.'%'];
        }
        if($order_sn){
            $where['t2.sn'] = ['like','%'.$order_sn.'%'];
        }
        if($khbh){
            $where['t2.khbh'] = ['like','%'.$khbh.'%'];
        }
        if($khmc){
            $where['t2.khmc'] = ['like','%'.$khmc.'%'];
        }
        if($sqzt){
            $where['t2.sqzt'] = $sqzt;
        }
        if($kind){
            $where['t.kind'] = $kind;
        }
        if($xslx){
            $where['t.xslx'] = $xslx;
        }
        if($sku){
            $where['t.sku'] = ['like','%'.$sku.'%'];
        }
        if($prod_sn){
            $where['t.prod_sn'] = ['like','%'.$prod_sn.'%'];
        }
        if($prod_name){
            $where['t.prod_name'] = ['like','%'.$prod_name.'%'];
        }
        if($category){
            $where['t.category'] = ['like','%'.$category.'%'];
        }
        if($start&&!$end){
            $where['t2.sqrq'] = ['>',strtotime($start)];
        }elseif(!$start&&$end){
            $where['t2.sqrq'] = ['<',strtotime($end)];
        }elseif($start&&$end){
            $where['t2.sqrq'] = [['>',strtotime($start)],['<',strtotime($end)],'and'];
        }
        $count = db('cg_order_list t')->join('cg_order t2','t.order_id=t.id')->where($where)->count();
        $data = db('cg_order_list t')->join('cg_order t2','t.order_id=t.id')->where($where)->field('zoning,provice,city,t2.sn,t2.khbh,t2.khmc,t2.sqzt,t.category,sku,prod_sn,prod_name,kind,xslx,unit,xgj,num,dzsl,hbsl,fhsl,shsl,yje,ysje,tj,zl,t2.id,product')->order('t2.id desc')->paginate(10,$count);
        $page = $data->render();
        $data = $data->items();
        foreach($data as $k=>$v){
            $v['sqzt'] = db('cg_order_status')->where('id',$v['sqzt'])->value('title');
            $v['guige'] = db('pro_products')->where('id',$v['product'])->value('format');
            $data[$k] = $v;
        }
        $this->assign([
            'page'=>$page,
            'data'=>$data,
        ]);
        $sqzts = db('cg_order_status')->order('ord asc')->select();
        $this->assign('sqzts',$sqzts);
        $dzsl = db('cg_order_list t')->join('cg_order t2','t.order_id=t.id')->where($where)->sum('t.dzsl');
        $num = db('cg_order_list t')->join('cg_order t2','t.order_id=t.id')->where($where)->sum('t.num');
        $ysje = db('cg_order_list t')->join('cg_order t2','t.order_id=t.id')->where($where)->sum('t.ysje');
        $dzsl = round($dzsl,4);
        $num = round($num,4);
        $ysje = round($ysje,4);
        $this->assign([
            'dzsl'=>$dzsl,
            'num'=>$num,
            'ysje'=>$ysje,
        ]);
        return $this->fetch();
    }
    /**
     * 销售订单明细汇总表
     */
    public function xsddmxhzb(){
        $zoning = $this->request->param('zoning');
        $provice = $this->request->param('provice');
        $city = $this->request->param('city');
        $khbh = $this->request->param('khbh');
        $khmc = $this->request->param('khmc');
        $brand = $this->request->param('brand');
        $category = $this->request->param('category');
        $series = $this->request->param('series');
        $sku = $this->request->param('sku');
        $prod_sn = $this->request->param('prod_sn');
        $prod_name = $this->request->param('prod_name');
        $start = $this->request->param('start');
        $end = $this->request->param('end');
        $this->assign([
            'zoning'=>$zoning,
            'provice'=>$provice,
            'city'=>$city,
            'khbh'=>$khbh,
            'khmc'=>$khmc,
            'brand'=>$brand,
            'category'=>$category,
            'series'=>$series,
            'sku'=>$sku,
            'prod_sn'=>$prod_sn,
            'prod_name'=>$prod_name,
            'start'=>$start,
            'end'=>$end,
        ]);
        $where = [];
        if($zoning){
            $where['t2.zoning'] = ['like','%'.$zoning.'%'];
        }
        if($provice){
            $where['t2.provice'] = ['like','%'.$provice.'%'];
        }
        if($city){
            $where['t2.city'] = ['like','%'.$city.'%'];
        }
        if($khbh){
            $where['t2.khbh'] = ['like','%'.$khbh.'%'];
        }
        if($khmc){
            $where['t2.khmc'] = ['like','%'.$khmc.'%'];
        }
        if($brand){
            $where['t.brand'] = ['like','%'.$brand.'%'];
        }
        if($category){
            $where['t.category'] = ['like','%'.$category.'%'];
        }
        if($category){
            $where['t.category'] = ['like','%'.$category.'%'];
        }
        if($series){
            $where['t.series'] = ['like','%'.$series.'%'];
        }
        if($sku){
            $where['t.sku'] = ['like','%'.$sku.'%'];
        }
        if($prod_sn){
            $where['t.prod_sn'] = ['like','%'.$prod_sn.'%'];
        }
        if($prod_name){
            $where['t.prod_name'] = ['like','%'.$prod_name.'%'];
        }
        if($start&&!$end){
            $where['t2.sqrq'] = ['>',$start];
        }elseif(!$start&&$end){
            $where['t2.sqrq'] = ['<',$end];
        }elseif($start&&$end){
            $where['t2.sqrq'] = [['>',$start],['<',$end],'and'];
        }
        $count  = db('cg_order_list t')->join('cg_order t2','t2.id=t.order_id')->where($where)->count();
        $data  = db('cg_order_list t')->join('cg_order t2','t2.id=t.order_id')->where($where)->field('category,sku,prod_name,unit,product,sum(num) ddsl,sum(dzsl) dzsl,sum(ysje) ddje')->group('product')->paginate(10,$count);
        $page = $data->render();
        $data = $data->items();
        $ddsl = 0;
        $dzsl = 0;
        $ddje = 0;
        foreach($data as $k=>$v){
            $ddsl += $v['ddsl'];
            $dzsl += $v['dzsl'];
            $ddje += $v['ddje'];
            $v['ddsl'] = round($v['ddsl'],4);
            $v['dzsl'] = round($v['dzsl'],4);
            $v['ddje'] = round($v['ddje'],4);
            $v['format'] = db('pro_products')->where('id',$v['product'])->value('format');
            $data[$k] = $v;
        }
        $ddsl = round($ddsl,4);
        $dzsl = round($dzsl,4);
        $ddje = round($ddje,4);
        $this->assign([
            'page'=>$page,
            'data'=>$data,
            'ddsl'=>$ddsl,
            'dzsl'=>$dzsl,
            'ddje'=>$ddje,
        ]);

        return $this->fetch();
    }

    /**
     * 未发货销售订单汇总
     */
    public function wfhxsddhz(){
        $zoning = $this->request->param('zoning');
        $provice = $this->request->param('provice');
        $city = $this->request->param('city');
        $khbh = $this->request->param('khbh');
        $khmc = $this->request->param('khmc');
        $brand = $this->request->param('brand');
        $category = $this->request->param('category');
        $series = $this->request->param('series');
        $sku = $this->request->param('sku');
        $prod_sn = $this->request->param('prod_sn');
        $prod_name = $this->request->param('prod_name');
        $start = $this->request->param('start');
        $end = $this->request->param('end');
        $this->assign([
            'zoning'=>$zoning,
            'provice'=>$provice,
            'city'=>$city,
            'khbh'=>$khbh,
            'khmc'=>$khmc,
            'brand'=>$brand,
            'category'=>$category,
            'series'=>$series,
            'sku'=>$sku,
            'prod_sn'=>$prod_sn,
            'prod_name'=>$prod_name,
            'start'=>$start,
            'end'=>$end,
        ]);
        $where = [];
        if($zoning){
            $where['t2.zoning'] = ['like','%'.$zoning.'%'];
        }
        if($provice){
            $where['t2.provice'] = ['like','%'.$provice.'%'];
        }
        if($city){
            $where['t2.city'] = ['like','%'.$city.'%'];
        }
        if($khbh){
            $where['t2.khbh'] = ['like','%'.$khbh.'%'];
        }
        if($khmc){
            $where['t2.khmc'] = ['like','%'.$khmc.'%'];
        }
        if($brand){
            $where['t.brand'] = ['like','%'.$brand.'%'];
        }
        if($category){
            $where['t.category'] = ['like','%'.$category.'%'];
        }
        if($category){
            $where['t.category'] = ['like','%'.$category.'%'];
        }
        if($series){
            $where['t.series'] = ['like','%'.$series.'%'];
        }
        if($sku){
            $where['t.sku'] = ['like','%'.$sku.'%'];
        }
        if($prod_sn){
            $where['t.prod_sn'] = ['like','%'.$prod_sn.'%'];
        }
        if($prod_name){
            $where['t.prod_name'] = ['like','%'.$prod_name.'%'];
        }
        if($start&&!$end){
            $where['t2.sqrq'] = ['>',$start];
        }elseif(!$start&&$end){
            $where['t2.sqrq'] = ['<',$end];
        }elseif($start&&$end){
            $where['t2.sqrq'] = [['>',$start],['<',$end],'and'];
        }
        $data  = db('cg_order_list t')->join('cg_order t2','t2.id=t.order_id')->where($where)->field('category,sku,prod_name,unit,product,sum(num) ddsl,sum(fhsl) fhsl,sum(ysje) ddje')->group('product')->select();
        /*Db::query('select category,sku,prod_name,unit,product,sum(num) ddsl,sum(fhsl) fhsl,sum(ysje) ddje from cg_order_list t join cg_order t2 on  t2.id=t.order_id group by t.category');*/
        $ddsl = 0;
        $fhsl = 0;
        $wfsl = 0;
        $wfje = 0;
        foreach($data as $k=>$v){
            $ddsl += $v['ddsl'];
            $fhsl += $v['fhsl'];
            $v['ddsl'] = round($v['ddsl'],4);
            $v['fhsl'] = round($v['fhsl'],4);
            $v['wfsl'] = $v['ddsl']-$v['fhsl'];
            $wfsl += $v['wfsl'];
            $v['ddje'] = round($v['ddje'],4);
            $v['wfje'] = $v['wfsl']/($v['ddsl']?$v['ddsl']:1)*$v['ddje'];
            $wfje += $v['wfje'];
            $v['format'] = db('pro_products')->where('id',$v['product'])->value('format');
            $data[$k] = $v;
        }
        $ddsl = round($ddsl,4);
        $fhsl = round($fhsl,4);
        $this->assign([
            'data'=>$data,
            'ddsl'=>$ddsl,
            'fhsl'=>$fhsl,
            'wfsl'=>$wfsl,
            'wfje'=>$wfje,
        ]);

        return $this->fetch();
    }
    /**
     * 未发货销售订单查询
     */
    public function wfhxsddcx(){
        $zoning = $this->request->param('zoning');
        $sn = $this->request->param('sn');
        $khmc = $this->request->param('khmc');
        $kind = $this->request->param('kind');
        $category = $this->request->param('category');
        $sku = $this->request->param('sku');
        $prod_name = $this->request->param('prod_name');
        $start = $this->request->param('start');
        $end = $this->request->param('end');
        $this->assign([
            'zoning'=>$zoning,
            'sn'=>$sn,
            'khmc'=>$khmc,
            'kind'=>$kind,
            'category'=>$category,
            'sku'=>$sku,
            'prod_name'=>$prod_name,
            'start'=>$start,
            'end'=>$end,
        ]);
        $where = [];
        if($zoning){
            $where['t.zoning'] = ['like','%'.$zoning.'%'];
        }
        if($sn){
            $where['t.sn'] = ['like','%'.$sn.'%'];
        }
        if($khmc){
            $where['t.khmc'] = ['like','%'.$khmc.'%'];
        }
        if($kind){
            $where['t2.kind'] = ['like','%'.$kind.'%'];
        }
        if($category){
            $where['t2.category'] = ['like','%'.$category.'%'];
        }
        if($sku){
            $where['t2.sku'] = ['like','%'.$sku.'%'];
        }
        if($prod_name){
            $where['t2.prod_name'] = ['like','%'.$prod_name.'%'];
        }
        if($start&&!$end){
            $where['t.sqrq'] = ['>',strtotime($start)];
        }elseif(!$start&&$end){
            $where['t.sqrq'] = ['<',strtotime($end)];
        }elseif($start&&$end){
            $where['t.sqrq'] = [['>',strtotime($start)],['<',strtotime($end)],'and'];
        }
        $count = db('cg_order t')->join('cg_order_list t2','t2.order_id=t.id')->where($where)->count();
        $data = db('cg_order t')->join('cg_order_list t2','t2.order_id=t.id')->where($where)->field('zoning,t.sn,khmc,category,sku,prod_name,t.id,product,t2.kind,t2.unit,t2.xgj,t2.num,t2.dzsl,t2.ysje,fhsl,beizhu')->order('t.id desc')->paginate(10,$count);
        $page = $data->render();
        $data = $data->items();
        foreach($data as $k=>$v){
            $v['format'] = db('pro_products')->where('id',$v['product'])->value('format');
            $data[$k] = $v;
        }
        $this->assign([
            'page'=>$page,
            'data'=>$data,
        ]);
        return $this->fetch();
    }
    /**
     * 所属网点订单汇总
     */
    public function sswdddhz(){
        $ywy = $this->request->param('ywy');
        $wdmc = $this->request->param('wdmc');
        $jxsmc = $this->request->param('jxsmc');
        $zoning = $this->request->param('zoning');
        $provice = $this->request->param('provice');
        $city = $this->request->param('city');
        $pslx = $this->request->param('pslx');
        $start = $this->request->param('start');
        $end = $this->request->param('end');
        $cpwd = $this->request->param('cpwd');
        $cpwd2 = $this->request->param('cpwd2/a');
        $cpwd3 = $this->request->param('cpwd3/a');
        $this->assign([
            'ywy'=>$ywy,
            'jxsmc'=>$jxsmc,
            'wdmc'=>$wdmc,
            'zoning'=>$zoning,
            'provice'=>$provice,
            'city'=>$city,
            'pslx'=>$pslx,
            'start'=>$start,
            'end'=>$end,
            'cpwd'=>$cpwd,
            'cpwd2'=>$cpwd2,
            'cpwd3'=>$cpwd3,
        ]);
        $where = [];
        $where['cusType'] = 2;
        $count = db('cg_order t')->join('cg_order_list t2','t2.order_id=t.id')
            ->where($where)
            ->count();
        $data = db('cg_order t')->join('cg_order_list t2','t2.order_id=t.id')
            ->where($where)
            ->paginate(10,$count);
        $page = $data->render();
        $data = $data->items();
        foreach($data as $k=>$v){
            $v['pslx'] = '经销商';
            $v['lxr'] = db('cg_net_ci')->where('net_id',$v['cusName'])->value('name');
            $v['lxdh'] = db('cg_net_ci')->where('net_id',$v['cusName'])->value('phone');
            $v['lxsj'] = db('cg_net_ci')->where('net_id',$v['cusName'])->value('mobile');
            $v['wddz'] = db('cg_net')->where('id',$v['cusName'])->value('detailAddress');
            $stuff = db('cg_net')->where('id',$v['cusName'])->value('stuff');
            $stuff = unserialize($stuff);
            $stuff = db('employee')->where('id','in',$stuff)->column('em_name');
            $v['ywy'] = implode(',',$stuff);
            $v['wdlx'] = db('cg_net t')->join('cg_networklevel t2','t.cat=t2.id')->where('t.id',$v['cusName'])->value('t2.title');
            $v['wddj'] = db('cg_net t')->join('cg_networktype t2','t.grade=t2.id')->where('t.id',$v['cusName'])->value('t2.title');
            $data[$k] = $v;
        }
        $this->assign([
            'page'=>$page,
            'data'=>$data,
        ]);
        return $this->fetch();
    }
    /**
     * 补货成本导入
     */
    public function bhcbdr(){
        $year = date('Y');
        $years = [
            $year+14,
            $year+13,
            $year+12,
            $year+11,
            $year+10,
            $year+9,
            $year+8,
            $year+7,
            $year+6,
            $year+5,
            $year+4,
            $year+3,
            $year+2,
            $year+1,
            $year,
            $year-1,
            $year-2,
            $year-3,
        ];
        $this->assign('years',$years);
        $khq1 = db('cg_kaoheqi1')->where(['starttime'=>['>=',strtotime(date('Y-1-1'))],'endtime'=>['<=',strtotime(date('Y-12-31 '))]])->select();
        $this->assign('khq1',$khq1);
        return $this->fetch();
    }

    //获取考核期-根据年份
    public function getkhq(){
        $year = $this->request->param('year');
        $khq1 = db('cg_kaoheqi1')->where(['starttime'=>['>=',strtotime($year.'-1-1')],'endtime'=>['<=',strtotime($year.'-12-31')]])->select();
        echo json_encode($khq1);
    }


    //导入
    public function upload()
    {
        // 获取表单上传文件
        $file = request()->file('file');

        // 移动到框架应用根目录/public/uploads/bx_guide 目录下
        $info = $file->validate(['ext' => 'xls,xlsx', 'size' => 105678])->move(ROOT_PATH . 'public' . DS . 'uploads' . DS . 'bx_guide');
        if ($info) {
            // 成功上传后 获取上传信息
            $data = self::testExcel($info->getSaveName());
            echo "<script>top.sweetAlert('" . $data['msg'] . "','','" . $data['success'] . "');parent.location.reload();</script>";
        } else {
            // 上传失败获取错误信息
            echo "<script>top.sweetAlert('" . $file->getError() . "','','warning');</script>";
        }
    }

    /**
     * excel导入示例
     */
    public function testExcel($file)
    {
        $khq = $this->request->param('khq');
        Vendor("PHPExcel");
        Vendor("PHPExcel.IOFactory");
        Vendor("PHPExcel.Reader.Excel5");

        $url = ROOT_PATH . 'public' . DS . 'uploads' . DS . 'bx_guide' . DS . $file;
        $objReader = PHPExcel_IOFactory::createReaderForFile($url);
        $objPHPExcel = $objReader->load($url);
        $sheet=$objPHPExcel->setActiveSheetIndex(0);
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $highestColumm = $sheet->getHighestColumn(); // 取得总列数

        $ins = [];
        for ($row = 2; $row <= $highestRow; $row++) {//行数是以第4行开始
            if (!trim($sheet->getCell('A' . $row)->getValue())) {
                break;
            }

            $t = [
                'kpmz'=>trim($sheet->getCell('A' . $row)->getValue()),
                'xm'=>trim($sheet->getCell('B' . $row)->getValue()),
                'wlbh'=>trim($sheet->getCell('C' . $row)->getValue()),
                'wlmc'=>trim($sheet->getCell('D' . $row)->getValue()),
                'xsbm'=>trim($sheet->getCell('E' . $row)->getValue()),
                'sdfbh'=>trim($sheet->getCell('F' . $row)->getValue()),
                'sdfmc'=>trim($sheet->getCell('G' . $row)->getValue()),
                'ckpz'=>trim($sheet->getCell('H' . $row)->getValue()),
                'ckx'=>trim($sheet->getCell('I' . $row)->getValue()),
                'xspz'=>trim($sheet->getCell('J' . $row)->getValue()),
                'cxxtddh'=>trim($sheet->getCell('K' . $row)->getValue()),
                'qkrq'=>trim($sheet->getCell('L' . $row)->getValue()),
                'xs'=>trim($sheet->getCell('M' . $row)->getValue()),
                'dzcb'=>trim($sheet->getCell('N' . $row)->getValue()),
                'djrq'=>trim($sheet->getCell('O' . $row)->getValue()),
                'cjrq'=>trim($sheet->getCell('P' . $row)->getValue()),
                'khq_id'=>$khq,
            ];
            $ins[] = $t;
        }
        $bool = db('cg_bhcb')->insertAll($ins);
        if(false!==$bool){
            echo "<script>top.sweetAlert('导入成功','','success');history.go(-1)</script>";
        }else{
            echo "<script>top.sweetAlert('导入失败','','warning');history.go(-1)</script>";
        }
        die;

    }

    /**
     * 生成出库单
     * scckd
     */
    public function scckd(){

        $id = $this->request->param('id');

       /* $data = db('cg_order')->where('id',$id)->select();
        $data['list'] = db('cg_order_list')->where('order_id',$id)->select();

        $bm = db('department t')->join('department_employee t2','t.id=t2.department_id')->where('t2.employee_id',session('employee_id'))->value('de_name');
        $mc = db('employee')->where('id',session('employee_id'))->value('em_name');
        $cg_cksqd = [];
        $cg_cksqd['sn'] = $this->produceCksqdSN();
        $cg_cksqd['sqr'] = session('employee_id');
        $cg_cksqd['sqrmc'] = $mc;
        $cg_cksqd['sqrbm'] = $bm;
        $cg_cksqd['sqrq'] = date('Y-m-d');
        $cg_cksqd['qrr'] = session('employee_id');
        $cg_cksqd['qrr'] = session('employee_id');
        $cg_cksqd['qrrmc'] = $mc;
        $cg_cksqd['qrrbm'] = $bm;
        $cg_cksqd['qrrq'] = date('Y-m-d');
        $cg_cksqd['ckfs'] = '销售订单';
        $cg_cksqd['dyck'] = '销售订单';*/
        return $this->fetch();
    }

    public function produceCksqdSN(){
        //生成编号
        $max = db('cg_cksqd')->where('sn','like',date('Ymd').'%')->max('sn'); //编号递增开始位置基础

        if($max){
            $max = (double)$max;
            $insert_sn = $max+1;
        }else{
            $insert_sn = date('Ymd').'0001';
        }
        return $insert_sn;
    }
    public function produceCksqdMxSN($order_id){
        //生成编号
        $max = db('cg_cksqd_list')->where(['order_id'=>$order_id])->max('sn'); //编号递增开始位置基础

        if($max){
            $max = (double)$max;
            $insert_sn = $max+1;
        }else{
            $insert_sn = 1;
        }
        return $insert_sn;
    }
}